using System;
using System.Collections;
using System.Collections.Generic;
using System.Diagnostics;
using System.Globalization;
using System.Linq;
using NHibernate;
using NHibernate.Criterion;
using SinBiota.Model.Business.MapServices.Datatypes;
using SinBiota.Model.Entities;
using SinBiota.Model.Exceptions;
using SinBiota.Model.Persistence.Repositories.Interfaces;

namespace SinBiota.Model.Persistence.Repositories.Implementation
{
    public class OccurrenceRepository : RepositoryAbstract<Occurrence,int>, IOccurrenceRepository
    {
        private readonly ITaxonRepository _taxonRepository;
        public OccurrenceRepository(ISession session, ITaxonRepository taxonRepository) : base(session)
        {
            _taxonRepository = taxonRepository;
        }

        public IEnumerable<Occurrence> FindByTaxonomicGroupId(int taxonomicGroupId)
        {
            try
            {
                var criteria = Session.CreateCriteria(typeof(Occurrence))
                    .CreateCriteria("SpecimenList")
                    .CreateCriteria("TaxonomicClassification")
                    .CreateCriteria("LegacyTaxonomicGroup")
                    .Add(Restrictions.Eq("id", taxonomicGroupId));
                return criteria.List<Occurrence>().Distinct();
            }
            catch (HibernateException exception)
            {
                throw new PersistenceException(exception.Message, exception);
            }
        }

        public IEnumerable<Occurrence> FindByKeyword(string keyword)
        {
            try
            {
                var criteria = Session.CreateCriteria(typeof(Occurrence))
                    .CreateCriteria("Keywords")
                    .Add(Restrictions.InsensitiveLike("Name", "%"+keyword+"%"));
                return criteria.List<Occurrence>().Distinct();
            }
            catch (HibernateException exception)
            {
                throw new PersistenceException(exception.Message, exception);
            }
        }

        public IEnumerable<Occurrence> FindByProjectCoordinator(string name)
        {
            try
            {
                var criteria = Session.CreateCriteria(typeof(Occurrence))
                    .CreateCriteria("Project")
                    .CreateCriteria("Coordinators")
                    .Add(Restrictions.InsensitiveLike("Name", "%" + name + "%"));
                return criteria.List<Occurrence>().Distinct();
            }
            catch (HibernateException exception)
            {
                throw new PersistenceException(exception.Message, exception);
            }
        }

        public IEnumerable<Occurrence> FindByProjectMember(string name)
        {
            try
            {
                var criteria = Session.CreateCriteria(typeof(Occurrence))
                    .CreateCriteria("Project")
                    .CreateCriteria("Members")
                    .Add(Restrictions.InsensitiveLike("Name", "%" + name + "%"));
                return criteria.List<Occurrence>().Distinct();
            }
            catch (HibernateException exception)
            {
                throw new PersistenceException(exception.Message, exception);
            }
        }

        public IEnumerable<Occurrence> FindByPublication(string name)
        {
            try
            {
                var criteria = Session.CreateCriteria(typeof (Occurrence))
                    .CreateCriteria("BibliographicalReference")
                    .Add(Restrictions.InsensitiveLike("Title", "%" + name + "%"));
                return criteria.List<Occurrence>().Distinct();
            }
            catch (HibernateException exception)
            {
                throw new PersistenceException(exception.Message, exception);
            }
        }

        public IEnumerable<Occurrence> FindByAmbient(Ambient ambient)
        {
            try
            {
                var criteria = Session.CreateCriteria(typeof(Occurrence))
                    .CreateCriteria("Location")
                    .Add(Restrictions.Eq("Ambient",ambient));
                return criteria.List<Occurrence>();
            }
            catch (HibernateException exception)
            {
                throw new PersistenceException(exception.Message, exception);
            }
        }

        public IEnumerable<Occurrence> FindByAuthorName(string name)
        {
            try
            {
                var criteria = Session.CreateCriteria(typeof (Occurrence))
                    .CreateCriteria("Author")
                    .Add(Restrictions.InsensitiveLike("Name", "%" + name + "%"));
                return criteria.List<Occurrence>();
            }
            catch (HibernateException exception)
            {
                throw new PersistenceException(exception.Message, exception);
            }
        }

        public IEnumerable<Occurrence> FindByMunicipalityId(int municipalityId)
        {
            try
            {
                var criteria = Session.CreateCriteria(typeof(Occurrence))
                    .CreateCriteria("Location")
                    .CreateCriteria("Municipality")
                    .Add(Restrictions.Eq("Id",municipalityId));
                return criteria.List<Occurrence>();
            }
            catch (HibernateException exception)
            {
                throw new PersistenceException(exception.Message, exception);
            }
            
        }

        public IEnumerable<Occurrence> FindByProjectId(int projectId)
        {
            try
            {
                var criteria = Session.CreateCriteria(typeof(Occurrence))
                    .CreateCriteria("Project")
                    .Add(Restrictions.Eq("Id", projectId));
                return criteria.List<Occurrence>();
            }
            catch (HibernateException exception)
            {
                throw new PersistenceException(exception.Message, exception);
            }
        }

        public IEnumerable<Occurrence> FindByUsername(string username)
        {
            try
            {
                var criteria = Session.CreateCriteria(typeof(Occurrence))
                    .CreateCriteria("User")
                    .Add(Restrictions.Eq("Username", username));
                return criteria.List<Occurrence>();
            }
            catch (HibernateException exception)
            {
                throw new PersistenceException(exception.Message, exception);
            }
            
        }

        public IEnumerable<LegacyTaxonomicGroup> FindAllLegacyTaxonomicGroups()
        {
            try
            {
                var criteria = Session.CreateCriteria(typeof (LegacyTaxonomicGroup));
                return criteria.List<LegacyTaxonomicGroup>();
            }
            catch (HibernateException exception)
            {
                throw new PersistenceException(exception.Message, exception);
            }
        }

        public IEnumerable<Occurrence> FindByTaxon(string searchField)
        {
            try
            {
                var criteria = Session.CreateCriteria(typeof(Occurrence))
                    .CreateCriteria("SpecimenList")
                    .CreateCriteria("TaxonomicClassification")
                    .Add(Restrictions.Like("LegacyTaxonomicClassification", "%" + searchField + "%"));
                return criteria.List<Occurrence>().Distinct();
            }
            catch (HibernateException exception)
            {
                throw new PersistenceException(exception.Message, exception);
            }
        }

        public IEnumerable<Occurrence> FindBySquareCoordinates(double nwLat, double nwLong, double seLat, double seLong)
        {
            if (nwLat > seLat)
            {
                var temp = nwLat;
                nwLat = seLat;
                seLat = temp;
            }
            if (nwLong > seLong)
            {
                var temp = nwLong;
                nwLong = seLong;
                seLong = temp;
            }
            return FindAll().Where(x => IsInsideSquare(nwLat, nwLong, seLat, seLong, x.Location.Latitude, x.Location.Longitude));
        }

        private static bool IsInsideSquare(double nwLat, double nwLong, double seLat, double seLong, double latitude, double longitude)
        {
            if (latitude < nwLat) return false;
            if (latitude > seLat) return false;
            if (longitude < nwLong) return false;
            if (longitude > seLong) return false;
            return true;
        }

        public int NumberOfTaxonomicGroups()
        {
            try
            {
                var criteria = Session.CreateCriteria(typeof(LegacyTaxonomicGroup))
                    .SetProjection(Projections.Count("Name"));
                return (int) criteria.UniqueResult();
            }
            catch (HibernateException exception)
            {
                throw new PersistenceException(exception.Message, exception);
            }
        }

        public int NumberOfAuthors()
        {
            try
            {
                var criteria = Session.CreateCriteria(typeof(OccurrenceAuthor))
                    .SetProjection(Projections.Count("Name"));
                return (int)criteria.UniqueResult();
            }
            catch (HibernateException exception)
            {
                throw new PersistenceException(exception.Message, exception);
            }
            
        }

        public IDictionary<string, int> OccurrencesByAmbient()
        {
            return CountGroupByQuery("SELECT Ambient, COUNT(*) FROM Location L, Occurrence O where O.Location_id = L.Id GROUP BY Ambient");
        }

        public IDictionary<string, int> OccurrencesByTaxonomicGroup()
        {
            return CountGroupByQuery("select Name, COUNT(*) as c from (SELECT O.Id, L.Name FROM Specimen S, LegacyTaxonomicGroup L, TaxonomicClassification T, Occurrence O where S.TaxonomicClassification_id = T.Id AND T.LegacyTaxonomicGroup_id = L.Id AND S.Occurrence_id = O.Id group by O.Id, L.Name) as sp group by Name order by c desc");
        }

        public IDictionary<string, int> OccurrencesByMonthYear()
        {
            var d = new Dictionary<DateTime, int>();
            foreach (var pair in CountGroupByQuery("select StartDate, COUNT(*) from Occurrence group by StartDate"))
            {
                string[] s;
                if (pair.Key.Contains("/"))
                {
                    s = pair.Key.Split('/');
                }
                else if (pair.Key.Contains("-"))
                {
                    s = pair.Key.Split('-');
                }
                else
                {
                    continue;
                }
                var k = new DateTime(int.Parse(s[2]), int.Parse(s[1]), 1);
                int count;
                if (d.TryGetValue(k, out count)) d[k] = count + pair.Value; else d[k] = pair.Value;
           }
            return d.OrderBy(x => x.Key).ToDictionary(x => String.Format("{0:00}/{1:00}", x.Key.Month, x.Key.Year),x => x.Value);
        }
        
        public IDictionary<string, int> SpecimensByAmbient()
        {
            return CountGroupByQuery("SELECT Ambient, COUNT(*) FROM Location L, Occurrence O, Specimen S where O.Location_id = L.Id and S.Occurrence_id = O.Id GRoup BY Ambient");
        }

        public IDictionary<string, int> OccurrencesByDrainageBasin()
        {
                return CountGroupByQuery("SELECT D.Name, COUNT(*) count FROM Location L, Occurrence O, DrainageBasin D where O.Location_id = L.Id AND D.Id = L.DrainageBasin_id GROUP BY D.Name ORDER BY count DESC");
        }

        public IDictionary<string, int> SpecimensByDrainageBasin()
        {
                return CountGroupByQuery("SELECT D.Name, COUNT(*) count FROM Location L, Occurrence O, Specimen S, DrainageBasin D where O.Location_id = L.Id AND D.Id = L.DrainageBasin_id AND S.Occurrence_id = O.Id  GROUP BY D.Name ORDER BY count DESC");
        }

        public IDictionary<string, int> OccurrencesByMunicipality()
        {
                return CountGroupByQuery("SELECT M.Name, COUNT(*) count FROM Location L, Occurrence O, Municipality M where O.Location_id = L.Id AND M.Id = L.Municipality_id GROUP BY M.Name ORDER BY count DESC");
        }
        public IDictionary<string, int> SpecimensByMunicipality()
        {
                return CountGroupByQuery("SELECT M.Name, COUNT(*) count FROM Location L, Occurrence O, Specimen S, Municipality M where O.Location_id = L.Id AND M.Id = L.Municipality_id AND S.Occurrence_id = O.Id GROUP BY M.Name ORDER BY count DESC");
        }

        public IDictionary<string, int> OccurrencesByConservationUnit()
        {
                return CountGroupByQuery("SELECT C.Name, COUNT(*) count FROM Location L, Occurrence O, ConservationUnit C where O.Location_id = L.Id AND C.Id = L.ConservationUnit_id GROUP BY C.Name ORDER BY count DESC");
        }
        public IDictionary<string, int> SpecimensByConservationUnit()
        {
                return CountGroupByQuery("SELECT C.Name, count(*) as c FROM Location L, Occurrence O, Specimen S, ConservationUnit C where O.Location_id = L.Id AND C.Id = L.ConservationUnit_id AND S.Occurrence_id = O.Id group by C.Name order by c desc");
        }

        public IDictionary<string, int> SpeciesByTaxonomicGroup()
        {
            return CountGroupByQuery("SELECT L.Name , COUNT(*) as c FROM Specimen S, LegacyTaxonomicGroup L, TaxonomicClassification T where S.TaxonomicClassification_id = T.Id AND T.LegacyTaxonomicGroup_id = L.Id GROUP BY L.Name order by c desc");
        }

        public IDictionary<string, int> SpeciesByMonthYear()
        {
            var d = new Dictionary<DateTime, int>();
            foreach (var pair in CountGroupByQuery("select O.StartDate, COUNT(*) from Occurrence O, Specimen S where S.Occurrence_id = O.Id group by StartDate order by StartDate"))
            {
                string[] s;
                if (pair.Key.Contains("/"))
                {
                    s = pair.Key.Split('/');
                }
                else if (pair.Key.Contains("-"))
                {
                    s = pair.Key.Split('-');
                }
                else
                {
                    continue;
                }
                var k = new DateTime(int.Parse(s[2]), int.Parse(s[1]), 1);
                int count;
                if (d.TryGetValue(k, out count)) d[k] = count + pair.Value; else d[k] = pair.Value;
            }
            return d.OrderBy(x => x.Key).ToDictionary(x => String.Format("{0:00}/{1:00}", x.Key.Month, x.Key.Year), x => x.Value);
        }

        public IDictionary<string, int> Top10Species()
        {
            return CountGroupByQuery(
                "select top 10 convert(varchar,T.Taxon_id), COUNT(*) as C from Specimen S, TaxonomicClassification T where S.TaxonomicClassification_id = T.Id group by Taxon_id order by C desc")
                .ToDictionary(x => _taxonRepository.FindById(int.Parse(x.Key)).ScientificName(), x => x.Value);
        }

        private IDictionary<string, int> CountGroupByQuery(string sql)
        {
            try
            {
                var query = Session.CreateSQLQuery(sql);
                IDictionary<string, int> result = new Dictionary<string, int>();
                foreach (var r in query.List<object[]>())
                {
                    result[(string)r[0]] = (int)r[1];

                }
                return result;
            }
            catch (HibernateException exception)
            {
                throw new PersistenceException(exception.Message, exception);
            }
            
        }


    }
}